package top.college.jjb;

public class JDBC_SQL_1 {

	public static final String LoginAdminSelect = "SELECT ADMIN_NAME FROM ADMIN_INFO WHERE ADMIN_ID = ? AND ADMIN_PW = ?";
	public static final String LoginProfSelect = "SELECT PROF_NAME FROM PROFESSOR WHERE PROF_NO = ? AND PROF_PW = ?";
	public static final String LoginStdSelect = "SELECT STD_NAME FROM STUDENT WHERE STD_NO = ? AND STD_PW = ?";

	public static final String DepartInfoSQL = "SELECT * FROM DEPART WHERE PROF_NO = ?";
	public static final String DepartInsertSQL = "INSERT INTO DEPART VALUES ( DEPART_SEQ.NEXTVAL, ?, ?, ?, ?, ?, SYSDATE )";
	public static final String DepartSelectAllSQL = "SELECT * FROM DEPART WHERE DEPT_NO = ?";
	public static final String DepartSelectAll1SQL = "SELECT D.DEPART_NAME, D.DEPART_TEL, D.DEPART_LOC, P.PORF_NAME, D.REG_DATE FROM DEPART D PROFESSOR P WHERE D.PROF_NO = P.PROF_NO ORDER BY DEPART_NO";
	
	public static final String AdminInsertSQL = " INSERT INTO ADMIN_INFO VALUES ( ADINFO.NEXTVAL, ?, ?, ?, ?, SYSDATE )";
	public static final String AdminSelectAllSQL = "SELECT * FROM ADMIN_INFO WHERE ADMIN_ID = ?";
	public static final String AdminSelectAll1SQL = "SELECT ADMIN_ID, ADMIN_NAME, ADMIN_TEL, REG_DATE FROM ADMIN_INFO ORDER BY ADMIN_TR";
	public static final String AdminSelectAll2SQL = "SELECT ADMIN_ID, ADMIN_NAME, ADMIN_TEL, REG_DATE FROM ADMIN_INFO WHERE ADMIN_ID = ?";
	public static final String AdminSelectIdSQL = "SELECT ADMIN_ID, ADMIN_NAME, ADMIN_TEL, REG_DATE FROM ADMIN_INFO WHERE ADMIN_ID = ? ORDER BY ADMIN_TR";
	public static final String AdminSelectNameSQL = "SELECT ADMIN_ID, ADMIN_NAME, ADMIN_TEL, REG_DATE FROM ADMIN_INFO WHERE ADMIN_NAME = ? ORDER BY ADMIN_TR";
	public static final String AdminUpdateSQL = "UPDATE ADMIN_INFO SET ADMIN_NAME = ?, ADMIN_PW = ?, ADMIN_TEL = ? WHERE ADMIN_ID = ?";
	public static final String AdminDeleteSQL = "DELETE FROM ADMIN_INFO WHERE ADMIN_ID = ?";
	
	public static final String ProfessorInsertSQL = "INSERT INTO PROFESSOR VALUES ( PRO_SEQ.NEXTVAL, ?, ?, ?, ?, ?,SYSDATE )";
	public static final String ProfessorSelectAllSQL = "SELECT * FROM PROFESSOR WHERE PROF_NO = ?";
	public static final String ProfessorSelectAll1SQL = "SELECT PROF_NO, PROF_NAME, PROF_TEL, PROF_ADDR, PROF_OFFICE, REG_DATE FROM PROFESSOR WHERE PROF_NO = ?";
	public static final String ProfessorSelectAll2SQL = "SELECT P.PROF_NO, P.PROF_NAME, P.PROF_TEL, D.DEPT_NAME, P.PROF_ADDR, P.PROF_OFFICE, P.REG_DATE FROM PROFESSOR P, DEPART D WHERE P.DEPT_NO = D.DEPT_NO ORDER BY P.PROF_TR";
	public static final String ProfessorSelectNoSQL = "SELECT P.PROF_NO, P.PROF_NAME, P.PROF_TEL, D.DEPT_NAME, P.PROF_ADDR, P.PROF_OFFICE, P.REG_DATE FROM PROFESSOR P, DEPART D WHERE P.DEPT_NO = D.DEPT_NO AND P.PROF_NO = ?";
	public static final String ProfessorSelectNameSQL = "SELECT P.PROF_NO, P.PROF_NAME, P.PROF_TEL, D.DEPT_NAME, P.PROF_ADDR, P.PROF_OFFICE, P.REG_DATE FROM PROFESSOR P, DEPART D WHERE P.DEPT_NO = D.DEPT_NO AND P.PROF_NAME = ?";
	public static final String ProfessorUpdateSQL = "UPDATE PROFESSOR SET PROF_PW = ?, PROF_NAME = ?, PROF_TEL = ?, PROF_OFFICE = ? WHERE PROF_NO = ?";
	public static final String ProfessorDeleteSQL = "DELETE FROM PROFESSOR WHERE PROF_NO = ?";
	
	public static final String StudentInsertSQL = "INSERT INTO STUDENT VALUES ( STD_SEQ.NEXTVAL, ?, ?, ?, ?, ?, ?, null, SYSDATE )";
	public static final String StudentSelectAllSQL = "SELECT * FROM STUDENT WHERE STD_NO = ?";
	public static final String StudentSelectAll1SQL = "SELECT S.STD_NO, S.STD_NAME, S.STD_TEL, S.STD_ADDR, D.DEPT_NO, S.REG_DATE FROM STUDENT S, DEPART D ORDER BY STD_TR";
	public static final String StudentSelectAll2SQL = "SELECT S.STD_NO, S.STD_NAME, S.STD_TEL, S.STD_ADDR, D.DEPT_NO, S.REG_DATE FROM STUDENT S, DEPART D WHERE STD_NO = ?";
	public static final String StudentSelectNoSQL = "SELECT S.STD_NO, S.STD_NAME, S.STD_TEL, S.STD_ADDR, D.DEPT_NO, S.REG_DATE FROM WHERE STD_NO = ? ";
	public static final String StudentSelectNameSQL = "SELECT S.STD_NO, S.STD_NAME, S.STD_TEL, S.STD_ADDR, D.DEPT_NO, S.REG_DATE FROM STUDENT S, DEPART D WHERE STD_NAME = ?";
	public static final String StudentUpdateSQL = "UPDATE STUDENT SET STD_NAME = ?, STD_PW = ?,  STD_TEL = ?, STD_ADDR = ? WHERE STD_NO = ?";
	public static final String StudentDeleteSQL = "DELETE FROM STUDENT WHERE STD_NO = ?";
}
